package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.format.Border;

//import jxl.Range;
//import jxl.Workbook;
//import jxl.format.Alignment;
//import jxl.format.Border;
//import jxl.format.BorderLineStyle;
//import jxl.format.CellFormat;
//import jxl.format.Colour;
//import jxl.format.Format;
//import jxl.format.VerticalAlignment;
//import jxl.write.Label;
//import jxl.write.Number;
//import jxl.write.NumberFormats;
//import jxl.write.WritableCellFormat;
//import jxl.write.WritableFont;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBCSACRInputFormAppend1;
import org.compiere.model.MBCSACRInputFormAppend2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_1;
import org.compiere.model.MBCSACRInputFormAppend3_1Ext;
import org.compiere.model.MBCSACRInputFormAppend3_2Ext;
import org.compiere.model.MBCSACRInputFormAppend4;
import org.compiere.model.MBCSACRInputFormCompany;
import org.compiere.model.MBCSACRInputFormPeriod;
import org.compiere.model.MBCSFRInputForm;
import org.compiere.model.MBCSFRInputFormColumn;
import org.compiere.model.MBCSFRInputFormCompany;
import org.compiere.model.MBCSFRInputFormLine;
import org.compiere.model.MBCSFRInputFormPeriod;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;
import org.utils.*;

public class BCS_IntercompanyStatusReport extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String Period = null;
	private BigDecimal Year = null;
	private int iYear = 0;
	private boolean isConsolidation = false;
	private BigDecimal C_BPartner1 = null;
	private BigDecimal C_BPartner2 = null;
	private String Application = "";
	private BigDecimal PeriodFrom = null;
	private MPeriod mperiod = null;
	private BigDecimal PeriodTo = null;
	private MPeriod mperiodTo = null;
	private BigDecimal Version = null;	
	
	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("PeriodFrom")) {
				PeriodFrom = (BigDecimal) para[i].getParameter();
			}else if (name.equals("PeriodTo")) {
				PeriodTo = (BigDecimal) para[i].getParameter();
			}else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			} else if (name.equals("C_BPartner_ID1")) {
				C_BPartner1 = (BigDecimal) para[i].getParameter();
			} else if (name.equals("C_BPartner_ID2")) {
				C_BPartner2 = (BigDecimal) para[i].getParameter();
			
			}else if (name.equals("BCS_Version")) {
				Version = (BigDecimal) para[i].getParameter();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		path = Util.localFilePath;
	}

	@Override
	protected String doIt() throws Exception {
		
		MBCSFRInputFormPeriod period = getMBCSFRInputFormPeriod(PeriodFrom, PeriodTo, Year, Version);
		List<MBCSFRInputFormCompany> company1 = null;
		List<MBCSFRInputFormCompany> company2 = null;
		
		if(period != null){
			company1 = getMBCSFRInputFormCompany(period.get_ID(), C_BPartner1);
			company2 = getMBCSFRInputFormCompany(period.get_ID(), C_BPartner2);
		}
		
		if(period == null || company1 == null || company1.size() == 0 || company2 == null || company2.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");

		
		String file = "BCS_IntercompanyStatusReport.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		//--------------------------------------------
		List<MBCSFRInputForm> forms1 = getMBCSFRInputForm(company1, "2,4,6");
		List<MBCSFRInputFormLine> lines1 = getMBCSFRInputFormLine(forms1);
		List<MBCSFRInputFormColumn> columns1 = getMBCSFRInputFormColumn(lines1);
		
		List<MBCSFRInputForm> forms2 = getMBCSFRInputForm(company2, "2,4,6");
		List<MBCSFRInputFormLine> lines2 = getMBCSFRInputFormLine(forms2);
		List<MBCSFRInputFormColumn> columns2 = getMBCSFRInputFormColumn(lines2);
		
		
		int countForm = forms1.size();
		int countLines = lines1.size();
		int countColumns = columns1.size();
		
		Row R;
		Cell C;
		MPeriod p1 = new MPeriod(Env.getCtx(), PeriodFrom.intValue(), null);
		MPeriod p2 = new MPeriod(Env.getCtx(), PeriodTo.intValue(), null);
		
		R = sheet.getRow(2);
		C = R.getCell(0);
		if(C == null)
			C = R.createCell(0);
		
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(p1.getStartDate());
		String value  = "Период с: ";
		value += org.utils.DataUtils.getMonthNameRU(calendar.getTime()) + " ";
		value += calendar.get(calendar.YEAR);
		
		C.setCellValue(value);
		
		R = sheet.getRow(3);
		C = R.getCell(0);
		if(C == null)
			C = R.createCell(0);
		
		calendar.setTime(p2.getEndDate());
		value  = "Период по: ";
		value += org.utils.DataUtils.getMonthNameRU(calendar.getTime()) + " ";
		value += calendar.get(calendar.YEAR);
		
		C.setCellValue(value);

		
		int iRow = 6;
		boolean yes = false;
		for(int iform = 0; iform < countForm; iform++){
			for(int iline = 0; iline < countLines; iline++)
				if(forms1.get(iform).get_ID() == lines1.get(iline).getBCS_FRInputForm_ID() && forms2.get(iform).get_ID() == lines2.get(iline).getBCS_FRInputForm_ID()){
					
					String value1 = "";
					String value2 = "";
					
					for(int icolumn = 0; icolumn < countColumns; icolumn++){
						
						if(lines1.get(iline).get_ID() == columns1.get(icolumn).getBCS_FRInputFormLine_ID())
							if(columns1.get(icolumn).getC_BPartner_ID() == C_BPartner2.intValue())
									value1 = columns1.get(icolumn).getValue1();
								
						if(lines2.get(iline).get_ID() == columns2.get(icolumn).getBCS_FRInputFormLine_ID())
							if(columns2.get(icolumn).getC_BPartner_ID() == C_BPartner1.intValue())
									value2 = columns2.get(icolumn).getValue1();	
//								&& lines2.get(iline).get_ID() == columns2.get(icolumn).getBCS_FRInputFormLine_ID()){
//							
//							System.out.println(columns1.get(icolumn).getC_BPartner_ID() + " : " + C_BPartner1.intValue() + " :: " + columns2.get(icolumn).getC_BPartner_ID() + " : " + C_BPartner2.intValue());
							
//							if(columns1.get(icolumn).getC_BPartner_ID() == C_BPartner1.intValue() && columns2.get(icolumn).getC_BPartner_ID() == C_BPartner2.intValue()){ 
//								
//								String value1 = columns1.get(icolumn).getValue1();
//								String value2 = columns2.get(icolumn).getValue1();
//								
//								if(!(value1.equals("0") && value2.equals("0")))
//									System.out.println(columns1.get(icolumn).get_ID() +" = "+ value1 + ":" + columns2.get(icolumn).get_ID()+" = "+value2);
//								
							
						
						
					}
					if(!(value1.equals("0") && value2.equals("0"))){
//						System.out.println(value1 + " : " + value2);
						yes = true;
						
						R = sheet.getRow(iRow);
						
							
						
						if(R == null)
							R = sheet.createRow(iRow);
						
							C = R.getCell(0);
							if(C == null)
								C = R.createCell(0);
							C.setCellValue(iRow-5);
							
							C = R.getCell(1);
							if(C == null)
								C = R.createCell(1);
							C.setCellValue("Обороты присутствуют");

							C = R.getCell(2);
							if(C == null)
								C = R.createCell(2);
							MBPartner bp = new MBPartner(Env.getCtx(), C_BPartner1.intValue(), null);
							C.setCellValue(bp.getName());

							C = R.getCell(3);
							if(C == null)
								C = R.createCell(3);
							bp = new MBPartner(Env.getCtx(), C_BPartner2.intValue(), null);
							C.setCellValue(bp.getName());
							
							
							Row r = sheet.getRow(6);
							for(int i=0; i < 4; i++){
								Cell c = r.getCell(i);
								Cell c2 = R.getCell(i);
								c2.setCellStyle(c.getCellStyle());
							}

							
//							iRow++;
					}
					
				}
		}
		
			R = sheet.getRow(iRow);
			
			
			
			if(R == null)
				R = sheet.createRow(iRow);
			
				C = R.getCell(0);
				if(C == null)
					C = R.createCell(0);
				C.setCellValue(iRow-5);
				
				C = R.getCell(1);
				if(C == null)
					C = R.createCell(1);
				if(!yes)
					C.setCellValue("Обороты отсутствуют");
				else{
//					company1.get(0)C.
					if(company1.size()>0 && company2.size()>0){
						String status1 = company1.get(0).getDocStatus();
						String status2 = company2.get(0).getDocStatus();
						if(status1.equals("AP")  && status2.equals("AP"))
							C.setCellValue("Данные приняты МК");
						else if((status1.equals("AP")  && status2.equals("CH")) || (status1.equals("CH")  && status2.equals("AP")))
							C.setCellValue("Данные сверены");
						else if(status1.equals("CH")  && status2.equals("CH"))
							C.setCellValue("Данные сверены");
						else if(status1.equals("LD")  || status2.equals("LD"))
							C.setCellValue("Данные не сверены");
						else 
							C.setCellValue("Данные не сверены");
					}
				}

				C = R.getCell(2);
				if(C == null)
					C = R.createCell(2);
				MBPartner bp = new MBPartner(Env.getCtx(), C_BPartner1.intValue(), null);
				C.setCellValue(bp.getName());

				C = R.getCell(3);
				if(C == null)
					C = R.createCell(3);
				bp = new MBPartner(Env.getCtx(), C_BPartner2.intValue(), null);
				C.setCellValue(bp.getName());
				
				
				Row r = sheet.getRow(6);
				for(int i=0; i < 4; i++){
					Cell c = r.getCell(i);
					Cell c2 = R.getCell(i);
				}
		
		
		
		
		//--------------------------------------------		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");

	}
	
	protected MBCSFRInputFormPeriod getMBCSFRInputFormPeriod(BigDecimal _periodFrom, BigDecimal _periodTo,  BigDecimal year,  BigDecimal Version) {
		MBCSFRInputFormPeriod period = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Period_ID FROM adempiere.BCS_FRInputForm_Period WHERE PeriodFrom = '"
				+ _periodFrom + "' AND PeriodTo = '"+ _periodTo +"'  AND C_Year_ID = '" + year + "' AND BCS_Version = '" + Version + "'";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				period = new MBCSFRInputFormPeriod(Env.getCtx(), rs.getInt(1),
						null);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return period;
	}

	
	protected List<MBCSFRInputFormCompany> getMBCSFRInputFormCompany(
			int MBCSFRInputFormPeriod_ID, BigDecimal c_bpartner) {
		List<MBCSFRInputFormCompany> company = new ArrayList<MBCSFRInputFormCompany>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_FRInputForm_Company_ID FROM adempiere.BCS_FRInputForm_Company \n WHERE BCS_FRInputForm_Period_ID = "
				+ MBCSFRInputFormPeriod_ID; 
		if(c_bpartner != null && c_bpartner.compareTo(new BigDecimal(0)) == 1)
				sql += " and C_BPartner_ID = " + c_bpartner;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				company.add(new MBCSFRInputFormCompany(Env.getCtx(),
						rs.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return company;
	}	
	
	
	private List<MBCSFRInputForm> getMBCSFRInputForm(List<MBCSFRInputFormCompany> companies, String codes) {
		
		List<MBCSFRInputForm> forms = new ArrayList<MBCSFRInputForm>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		for(MBCSFRInputFormCompany company: companies){
			sql = "SELECT f.BCS_FRInputForm_ID, f.BCS_FRSetupInputForm_ID, s.name, s.code FROM adempiere.BCS_FRInputForm f	\n"
					+ "	inner join adempiere.BCS_FRSetupInputForm s on s.BCS_FRSetupInputForm_ID = f.BCS_FRSetupInputForm_ID	\n"
					+ "	WHERE  BCS_FRInputForm_Company_ID = " + company.get_ID()
					+ "	and s.code in ("+codes+")	";

	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputForm form = new MBCSFRInputForm(Env.getCtx(), rs.getInt(1),	null);
					form.setName(rs.getString(3));
					form.setCode(rs.getInt(4));
					forms.add(form);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
//		

		return forms;
	}
	
	private List<MBCSFRInputFormLine> getMBCSFRInputFormLine(
			List<MBCSFRInputForm> forms) {
		
		List<MBCSFRInputFormLine> lines = new ArrayList<MBCSFRInputFormLine>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputForm form: forms){
		
			sql = "SELECT BCS_FRInputFormLine_ID FROM adempiere.BCS_FRInputFormLine WHERE  BCS_FRInputForm_ID = " + form.get_ID()
					+ " order by BCS_FRInputFormLine_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormLine line = new MBCSFRInputFormLine(Env.getCtx(), rs.getInt(1),	null);
					lines.add(line);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return lines;
	}

	private List<MBCSFRInputFormColumn> getMBCSFRInputFormColumn(
			List<MBCSFRInputFormLine> lines) {
		
		List<MBCSFRInputFormColumn> columns = new ArrayList<MBCSFRInputFormColumn>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSFRInputFormLine line: lines){
		
			sql = "SELECT BCS_FRInputFormColumn_ID FROM adempiere.BCS_FRInputFormColumn WHERE  BCS_FRInputFormLine_ID = " + line.get_ID()
					+ " order by seqno ";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					MBCSFRInputFormColumn column = new MBCSFRInputFormColumn(Env.getCtx(), rs.getInt(1),	null);
					columns.add(column);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "CalloutBCS_FRInputCompanyUpload", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return columns;
	}
	

}